package hw3.proxy.core;

import hw3.exception.StdErrReporterException;
import hw3.sql.SQLDBManager;

import java.io.IOException;
import java.net.URI;
import java.net.URISyntaxException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

/**
 * This class is responsible for storing and retrieving resources in the DB.
 * It also allows querying the DB.
 * <p>
 * note: since we <b>cannot assume</b> that we are connected to the DB at ANY time, then:
 * <ol>
 * <li>connection validation is made every time we try to access the DB.</li>
 * <li>we avoid preparing statement for add/delete/etc... since the prepared statement
 * might not be valid when used (this was also made for simplicity).</li>
 * </ol>
 * </p>
 * 
 * @author Adam Levi
 * @author Marina Skarbovsky
 *
 */
public class ProxyDBManager extends SQLDBManager
{
	/**
	 * creates a ProxyDBManager object 
	 * 
	 * @param host The DB host name
	 * @param port The DB port number
	 * @param dbName The DB name at the host
	 * @param username The user name that will be used to connect to the DB
	 * @param password The user name that will be used to connect to the DB
	 */
	public ProxyDBManager(
			String host, int port, String dbName,
			String username, String password)
	{
		super(host, port, dbName, username, password);
	}
	
	/**
	 * construct the cache table. 
	 */
	public void constructCacheTables() throws SQLException, StdErrReporterException, ClassNotFoundException
	{
		createTable(CacheTableName, ColumnNames, ColumnSQLDataTypes, PrimaryKey, PrimaryKeyLength);
	}
	
	/**
	 * deletes the cache table. 
	 */
	public void deleteCacheTables() throws SQLException, StdErrReporterException, ClassNotFoundException
	{
		dropTable(CacheTableName);		
	}
	
	/**
	 * clears the cache table. 
	 */
	public void clearCache() throws SQLException, ClassNotFoundException
	{
		clearTable(CacheTableName);
	}
	
	/**
	 * Poses a query on the cache table
	 * @param query The string that represents the query
	 * @param outputToConsole a boolean that indicates if we need to output the result to the Console
	 * @param outputToFrame a boolean that indicates if we need to output the result to a Frame (dialog)
	 * @param outputToXHTMLFile a boolean that indicates if we need to output the result to an XHTML file
	 * @param xhtmlOutputFileName a String that is the path for the XHTML (only relevant if outputToXHTMLFile==true)
	 * @param autoOpenBrowser a boolean that indicates if we need to open the browser
	 * after the XHTML result file is made (only relevant if outputToXHTMLFile==true)
	 */
	public void poseQuery(
			String query,
			boolean outputToConsole,
			boolean outputToFrame,
			boolean outputToXHTMLFile,
			String xhtmlOutputFileName,
			boolean autoOpenBrowser) throws StdErrReporterException, SQLException, ClassCastException, ClassNotFoundException, InstantiationException, IllegalAccessException, IOException, URISyntaxException
	{
		ResultSet rs = null;
		try
		{
			 rs = doQuery(query);
			 ArrayList<Object[]> rows = null; 
			 java.sql.ResultSetMetaData rsMeta = rs.getMetaData();
			 if (outputToConsole)
			 {
				 rows = SQLDBManager.printQueryToConsole(query, rs);
			 }
			 if (rows == null && (outputToFrame || outputToXHTMLFile))
			 {
				 rows = getRows(rs);
			 }
			 if (outputToFrame)
			 {
				 SQLDBManager.printQueryToFrame(query, rows, rsMeta);
			 }
			 if (outputToXHTMLFile)
			 {
				 SQLDBManager.printQueryToXHTMLFile(xhtmlOutputFileName, query, rows, rsMeta);
				 if (autoOpenBrowser)
				 {
					 java.awt.Desktop.getDesktop().browse(new URI(xhtmlOutputFileName));
				 }
			 }
 
		}
		finally
		{
			if (rs != null)
			{
				rs.close();
			}
			afterQuery();
		}
		
	}
	
	/**
	 * checks if the given entry exists in the DB (based on the URL column)
	 * if it does - it will be replaced with the new entry,
	 * otherwise the new entry will be entered to the cache.
	 * @param cacheEntry - an array that represents a cache entry
	 */
	public void updateRow(String[] cacheEntry) throws SQLException, ClassNotFoundException
	{
		// delete row
		String url = cacheEntry[0];
		deleteRow(url);
		
		// insert new
		insertRow(cacheEntry);
		
	}
	
	/**
	 * inserts the given cache entry to the cache table
	 * (based on the URL column) if it does - it will be replaced with the
	 * new entry, otherwise the new entry will be entered to the cache
	 * @param cacheEntry - an array that represents a cache entry
	 */
	public void insertRow(String[] cacheEntry) throws SQLException, ClassNotFoundException
	{
		PreparedStatement stmt = this.getPreparedAddRow(CacheTableName, ColumnNames.length);
		try
		{
			for (int i = 0; i < cacheEntry.length; i++ )
			{
				stmt.setString(i + 1, cacheEntry[i]);
			}
			stmt.executeUpdate();
		}
		finally
		{
			SQLDBManager.ClosePreparedStatement(stmt);
		}
		
	}
	
	/**
	 * deletes a row from the cache table
	 * @param url The URL that indicates the row to delete
	 */
	public void deleteRow(String url) throws SQLException, ClassNotFoundException
	{
		PreparedStatement stmt = this.getPreparedDeleteRow(CacheTableName, ColumnNames[URL]);
		try
		{
			stmt.setString(1, url);
			stmt.executeUpdate();
		}
		finally
		{
			SQLDBManager.ClosePreparedStatement(stmt);
		}
	}
	/**
	 * Tests if the table already contains a specific row
	 * @param url The URL that indicates the row
	 * @return true if and only if the table contains the row
	 */
	public boolean containsCacheEntry(String url) throws SQLException, ClassNotFoundException
	{
		return (getCacheEntry(url) != null);
	}
	
	/**
	 * Retrieves a cache entry from the table
	 * @param url The URL that indicates the row
	 * @return an array that represents the cache entry in case of success, null otherwise
	 */
	public String[] getCacheEntry(String url) throws SQLException, ClassNotFoundException
	{
		ResultSet rs = null;
		PreparedStatement preStmt = null;
		try
		{
			validateConnection();
			String query = 	"SELECT * FROM Cache WHERE (Url = ?)";
			preStmt = (PreparedStatement) m_Connection.prepareStatement(query);
			preStmt.setString(1, url);

			rs = preStmt.executeQuery();

			if (rs.next())
			{
				String[] res = new String[ColumnNames.length];
				for (int i = 0; i < ColumnNames.length; i++)
				{
					res[i] = rs.getString(i+1);
				}
				return res;
			}
			else
			{
				return null;
			}
		}
		finally
		{
			SQLDBManager.ClosePreparedStatement(preStmt);
			SQLDBManager.CloseResultSet(rs);
		}
	}

	/**
	 * closes the connection to the server
	 */
	public void terminate() throws SQLException
	{
		dispose();
	}
	
	public static final int URL 	 	= 0;
	public static final int RESPONSE 	= 1;
	public static final int LASMODIFIED = 2;
	public static final int EXPIRES 	= 3;
	public static final int ETAG 		= 4;
	
	static final Integer 	PrimaryKey = URL;
	static final Integer 	PrimaryKeyLength = 767;
	static final String 	CacheTableName = "Cache";
	static final String[] 	ColumnNames =
	{
		"URL", "Response", "LastModified", "Expires", "ETag"
	};
	
	static final String[] ColumnSQLDataTypes = 
	{
		"TEXT", 		/* URL */
		"MEDIUMTEXT",	/* Response */
		"TINYTEXT",		/* LastModified */
		"TINYTEXT",		/* Expires */
		"TINYTEXT"		/* ETag */
	};
	
}
